package dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.security.spec.ECField;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.elasticsearch.index.query.MatchAllQueryBuilder;
import org.elasticsearch.index.query.QueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.els.ElsConnection;
import com.els.factory.ElasticFactory;
import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.webapp.dao.CounselorDAO;
import com.webapp.dao.CounselorDetailDAO;
import com.webapp.dao.SSCB_TJ_ALL_NEW_DAO;
import com.webapp.domain.entity.AssessmentData;
import com.webapp.domain.entity.Counselor;
import com.webapp.domain.entity.CounselorDetail;
import com.webapp.domain.entity.SSCB_TJ_ALL_NEW;

@RunWith(SpringJUnit4ClassRunner.class)   
@ContextConfiguration(locations={"classpath:spring/spring-applicationContext.xml","classpath:spring/spring-servlet.xml"})

public class SSCE_TJ_ALL_NEW_DAO_TEST {

	
	  @Autowired
	  SSCB_TJ_ALL_NEW_DAO sscb_TJ_ALL_NEW_DAO;
	  @Autowired  
	    SessionFactory sessionFactory; 
	  public Session getSession(){
	    	return sessionFactory.openSession();
	    }
	    
	  
	  @Test
	  public void insertSSCE_TJ_ALL_NEW() throws Exception{
		  File file=new File("C:\\Users\\Administrator\\Desktop\\odr\\csv\\诉讼成本数据-20170315 - dao.xlsx");
		  FileInputStream is=new FileInputStream(file);
		  XSSFWorkbook xxx = new XSSFWorkbook(is); 
		  XSSFSheet sheet = xxx.getSheetAt(0);
		  int trlength=sheet.getLastRowNum();//总行数
		  SSCB_TJ_ALL_NEW sscb_TJ_ALL_NEW = null;
//	   		HSSFRow row=sheet.getRow(0);
		  XSSFRow row=sheet.getRow(1);
//	   		int tdlength=row.getLastCellNum();//总列数
		  JsonObject jsonObject = null;
		  Session session = getSession();
		  for (int i = 1; i < trlength+1; i++) {	
			  //XSSFCell cell=sheet.getRow(i).getCell(13);
			  XSSFRow r=sheet.getRow(i);
			  sscb_TJ_ALL_NEW = new SSCB_TJ_ALL_NEW();
			  sscb_TJ_ALL_NEW.setCAUSE_ASACATION_NAME(r.getCell(0).toString());
			  sscb_TJ_ALL_NEW.setGCASE_NUM(getToInteger(r.getCell(1)));
			  sscb_TJ_ALL_NEW.setCASE_MONEY_NUM(getToBigDecimal(r.getCell(2)));
			  sscb_TJ_ALL_NEW.setSLTS_NUM(getToBigDecimal(r.getCell(3)));
			  sscb_TJ_ALL_NEW.setCASE_TRIAL_NUM(getToBigDecimal(r.getCell(4)));
			  sscb_TJ_ALL_NEW.setLJXWFLWT_NUM(getToBigDecimal(r.getCell(5)));
			  sscb_TJ_ALL_NEW.setZBSSCLSJ_NUM(getToBigDecimal(r.getCell(6)));
			  sscb_TJ_ALL_NEW.setNXQSZSJ_NUM(getToBigDecimal(r.getCell(7)));
			  sscb_TJ_ALL_NEW.setTJQSZSJ_NUM(getToBigDecimal(r.getCell(8)));
			  sscb_TJ_ALL_NEW.setSSFJNSJ_NUM(getToBigDecimal(r.getCell(9)));
			  sscb_TJ_ALL_NEW.setSZTJSJ_NUM(getToBigDecimal(r.getCell(10)));
			  sscb_TJ_ALL_NEW.setZJJHSJ_NUM(getToBigDecimal(r.getCell(11)));
			  sscb_TJ_ALL_NEW.setBCCLSJ_NUM(getToBigDecimal(r.getCell(12)));
			  sscb_TJ_ALL_NEW.setKTCPSJ_NUM(getToBigDecimal(r.getCell(13)));
			  sscb_TJ_ALL_NEW.setCJTSSJ_NUM(getToBigDecimal(r.getCell(14)));
			  sscb_TJ_ALL_NEW.setDTXPSJ_NUM(getToBigDecimal(r.getCell(15)));
			  sscb_TJ_ALL_NEW.setSSFJSSJ_NUM(getToBigDecimal(r.getCell(16)));
			  sscb_TJ_ALL_NEW.setSQLAZXSJ_NUM(getToBigDecimal(r.getCell(17)));
			  sscb_TJ_ALL_NEW.setTGXSCYSJ_NUM(getToBigDecimal(r.getCell(18)));
			  sscb_TJ_ALL_NEW.setCYZXYP_NUM(getToBigDecimal(r.getCell(19)));
			  sscb_TJ_ALL_NEW.setAJSLF_NUM(getToBigDecimal(r.getCell(20)));
			  sscb_TJ_ALL_NEW.setLSF_NUM(getToBigDecimal(r.getCell(21)));
			  sscb_TJ_ALL_NEW.setWGF_NUM(getToBigDecimal(r.getCell(22)));
			  sscb_TJ_ALL_NEW.setJJCB_NUM(getToBigDecimal(r.getCell(23)));
			  sscb_TJ_ALL_NEW.setZX_GCASE_NUM(getToInteger(r.getCell(24)));
			  sscb_TJ_ALL_NEW.setZXTS_NUM(getToBigDecimal(r.getCell(25)));
			  sscb_TJ_ALL_NEW.setZXWB_GCASE_NUM(getToBigDecimal(r.getCell(26)));
			  sscb_TJ_ALL_NEW.setYGSS_GCASE_NUM(getToBigDecimal(r.getCell(27)));
			  sscb_TJ_ALL_NEW.setSSJE_NUM(getToBigDecimal(r.getCell(28)));
			  sscb_TJ_ALL_NEW.setTJ_GCASE_NUM(getToBigDecimal(r.getCell(29)));
			  sscb_TJ_ALL_NEW.setTJ_SLTS_NUM(getToBigDecimal(r.getCell(30)));
			  sscb_TJ_ALL_NEW.setCASE_END_AMOU_HJ(getToBigDecimal(r.getCell(31)));
			  sscb_TJ_ALL_NEW.setTJ_AJSLF_NUM(getToBigDecimal(r.getCell(32)));
			  sscb_TJ_ALL_NEW.setTJ_LSF_NUM(getToBigDecimal(r.getCell(33)));
			  sscb_TJ_ALL_NEW.setTJ_WGF_NUM(getToBigDecimal(r.getCell(34)));
			  sscb_TJ_ALL_NEW.setSUM_GCASE_NUM(getToInteger(r.getCell(35)));
			  sscb_TJ_ALL_NEW.setGCASE_NUM_2013(getToInteger(r.getCell(36)));
			  sscb_TJ_ALL_NEW.setGCASE_NUM_2014(getToInteger(r.getCell(37)));
			  sscb_TJ_ALL_NEW.setGCASE_NUM_2015(getToInteger(r.getCell(38)));
			  sscb_TJ_ALL_NEW.setGCASE_NUM_2016(getToInteger(r.getCell(39)));
			  
			  long id = (Long) session.save(sscb_TJ_ALL_NEW);
			  System.err.println(id);
			  
			  
		  }
		  System.err.println("完成");
	  }
	  
	  private static BigDecimal getToBigDecimal(Cell cell){
		  if (cell==null || cell.toString().equals("")) {
			return new BigDecimal(0);
		  }
		  try {
			return new BigDecimal(cell.toString());
		} catch (Exception e) {
			System.out.println(cell);
			// TODO: handle exception
		}
		  return null;
	  }
	  private static Integer getToInteger(Cell cell){
		  String string = cell.toString();
		  if (string.indexOf(".")>=0) {
			string = string.substring(0, string.indexOf("."));
		}
		  return Integer.valueOf(string);
	  }
	  
	  
	  /**
	   * 格式化专业范围
	   * @param string
	   * @return
	   */
	  public static JsonArray StringToJsonArray(String string){
			String[] array=string.split("、");	
			StringBuffer buffer=new StringBuffer();
			buffer.append("[");
			for (int k = 0; k < array.length; k++) {
				array[k]="'"+array[k]+"'";
			}
			for (int m = 0; m < array.length; m++) {
				buffer.append(array[m]);
				if(m!=array.length-1){
					buffer.append(",");
				}
			}
			buffer.append("]");
			 JsonArray jsonArray = (JsonArray) new Gson().fromJson(buffer.toString(), JsonArray.class);
		  return jsonArray;
	  }
	  
	  
	  /**
	   * 修改图片名字
	   * @param imgPath
	   * @param id
	   * @return
	   */
	  public static String updataImgName(String imgPath,String index,Long id){
		 
		  File imgFile = new File(imgPath);
		  File[] imgs = imgFile.listFiles();
		  for (File file : imgs) {
			String imgName = file.getName();
			if (imgName.startsWith(index)) {
				String end = imgName.substring(imgName.lastIndexOf("."));
				file.renameTo(new File(imgPath+"zixunshi_"+id+end));
				return "zixunshi_"+id+end;
			}
		  }
		  return null;
	  }
	  private String parseExcel(Cell cell) { 
		    if (cell==null) {
				return "";
			}
	        String result = new String();  
	        switch (cell.getCellType()) {  
	        case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
	            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
	                SimpleDateFormat sdf = null;  
	                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
	                        .getBuiltinFormat("h:mm")) {  
	                    sdf = new SimpleDateFormat("HH:mm");  
	                } else {// 日期  
	                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
	                }  
	                Date date = cell.getDateCellValue();  
	                result = sdf.format(date);  
	            } else if (true) {  //cell.getCellStyle().getDataFormat() == 58
	                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
	                SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月");  
	                double value = cell.getNumericCellValue();  
	                Date date = org.apache.poi.ss.usermodel.DateUtil  
	                        .getJavaDate(value);  
	                result = sdf.format(date);  
	            } else {  
	                double value = cell.getNumericCellValue();  
	                CellStyle style = cell.getCellStyle();  
	                DecimalFormat format = new DecimalFormat();  
	                String temp = style.getDataFormatString();  
	                // 单元格设置成常规  
	                if (temp.equals("General")) {  
	                    format.applyPattern("#");  
	                }  
	                result = format.format(value);  
	            }  
	            break;  
	        case HSSFCell.CELL_TYPE_STRING:// String类型  
	            result = cell.getRichStringCellValue().toString();  
	            break;  
	        case HSSFCell.CELL_TYPE_BLANK:  
	            result = "";  
	        default:  
	            result = "";  
	            break;  
	        }  
	        return result;  
	    } 
	  public String getCell(HSSFCell cell) {
			DecimalFormat df = new DecimalFormat("#");
			if (cell == null)
				return "";
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				if(HSSFDateUtil.isCellDateFormatted(cell)){
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
				}
				return df.format(cell.getNumericCellValue());
			case HSSFCell.CELL_TYPE_STRING:
				System.out.println(cell.getStringCellValue());
				return cell.getStringCellValue();
			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			case HSSFCell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case HSSFCell.CELL_TYPE_ERROR:
				return cell.getErrorCellValue() + "";
			}
			return "";
		}
	  
	  
	  
	  
	  
	  @Test
	  public void uodate(){
		  String indexName ="xxx";
		  ElsConnection ec = ElasticFactory.getConnection();

		  boolean is = ec.deleteOne(indexName, indexName, "AVomo4jpHFp9vHOxLX2r");
		  System.err.println(is);
		  
	  }


	  @Test
	  public void delete2(){
		 
		  String indexName = "xxx";
		 
			  
			  ElsConnection ec = ElasticFactory.getConnection();
			 
			 
					  boolean is = ec.deleteOne(indexName, indexName, "AVqS7Ublxgurc9r6WAD4");
					 
		System.err.println(is);
		  
	  }

	  
}
